package com.situ.company.web220111.employee.dao.impl;

import com.situ.company.web220111.department.model.DepartmentModel;
import com.situ.company.web220111.employee.dao.IEmployeeDao;
import com.situ.company.web220111.employee.model.EmployeeModel;
import com.situ.company.web220111.util.FmtEmpty;
import com.situ.company.web220111.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.*;

public class EmployeeDaoImpl implements IEmployeeDao {
    private static String table = "employee";
    private static String cols = "code,name,pass,code_dept,image";
    private Connection conn;
    private PreparedStatement ps;

    @Override
    public String insert(EmployeeModel model) {
        StringBuffer sql = new StringBuffer("insert into ").append(table).append('(').append(cols).append(")values(?,?,?,?,?)");
        return JDBCUtil.update(sql.toString(), model.getCode(), model.getName(), model.getPass(), model.getCodeDept(), model.getImage());
    }

    @Override
    public String deleteByCode(EmployeeModel model) {
        String sql = String.format("delete from %s where code = ? ", table);
        return JDBCUtil.update(sql, model.getCode());
    }

    @Override
    public String delete(EmployeeModel model) {
        StringBuffer sql = new StringBuffer("delete from ").append(table);
        List<Object> values = appendWhere(model, sql);
        return JDBCUtil.update(sql.toString(), values);
    }


    @Override
    public String updateAll(EmployeeModel model) {
        String sql = String.format("update %s set name = ?,code_dept = ? where code = ?", table);
        return JDBCUtil.update(sql, model.getName(), model.getCodeDept(), model.getCode());
    }

    @Override
    public String updateActive(EmployeeModel model) {
        StringBuffer sql = new StringBuffer("update ");
        List<Object> vals = appendSet(model, sql);
        return JDBCUtil.update(sql.toString(), vals);
    }

    private List<Object> appendSet(EmployeeModel model, StringBuffer sql) {
        sql.append(table).append(" set id = id ");
        List<Object> vals = new ArrayList<>();
        String name = model.getName();
        if (name != null) { // name == null 没有对象|| name.trim().isEmpty() 有对象但是没有数据
            sql.append(", name = ? ");
            vals.add(name);
        }
        String pass = model.getPass();
        if (pass != null) { // name == null 没有对象|| name.trim().isEmpty() 有对象但是没有数据
            sql.append(",pass = ? ");
            vals.add(pass);
        }
        String codeDept = model.getCodeDept();
        if (codeDept != null) { // name == null 没有对象|| name.trim().isEmpty() 有对象但是没有数据
            sql.append(",code_dept = ? ");
            vals.add(codeDept);
        }
        String image = model.getImage();
        if (image != null) { // name == null 没有对象|| name.trim().isEmpty() 有对象但是没有数据
            sql.append(",image = ? ");
            vals.add(image);
        }
        sql.append("where code = ?");
        vals.add(model.getCode());
        return vals;
    }

    @Override
    public List<EmployeeModel> selectList(EmployeeModel model) {
        StringBuffer sql = new StringBuffer("select id,").append(cols).append(",(select name from department where code=code_dept) name_dept ").append(" from ").append(table);
        List vals = appendWhere(model, sql);
        Map<String, String> fields = new HashMap<>();
        fields.put("id", "id");
        fields.put("code", "code");
        fields.put("name", "name");
        fields.put("pass", "pass");
        fields.put("codeDept", "code_dept");
        fields.put("image", "image");
        fields.put("nameDept","name_dept");
        return JDBCUtil.querylist(sql.toString(), vals, EmployeeModel.class, fields);
    }

    @Override
    public EmployeeModel selectModel(EmployeeModel model) {
        StringBuffer sql = new StringBuffer("select id,").append(cols).append(",(select name from department where code=code_dept) name_dept ").append(" from ").append(table).append(" where code = ? ");
        List<Object> values = Arrays.asList(model.getCode());
        Map<String, String> fields = new HashMap<>();
        fields.put("id", "id");
        fields.put("code", "code");
        fields.put("name", "name");
        fields.put("pass", "pass");
        fields.put("codeDept", "code_dept");
        fields.put("image", "image");
        fields.put("nameDept", "name_dept");
        return JDBCUtil.queryModel(sql.toString(), values, EmployeeModel.class, fields);
    }


    private List<Object> appendWhere(EmployeeModel model, StringBuffer sql) {
        sql.append(" where 1=1 ");
        List<Object> vals = new ArrayList<>();
        String code = model.getCode();
        if (!FmtEmpty.isEmpty(code)) {
            sql.append(" and code like ? ");
            vals.add(code);
        }
        String name = model.getName();
        if (!FmtEmpty.isEmpty(name)) {
            sql.append(" and name like ? ");
            vals.add(name);
        }
        String pass = model.getPass();
        if (!FmtEmpty.isEmpty(pass)) {
            sql.append(" and pass like ? ");
            vals.add(pass);
        }
        String codeDept = model.getCodeDept();
        if (!FmtEmpty.isEmpty(codeDept)) {
            sql.append(" and code_dept like ? ");
            vals.add(codeDept);
        }
        if (model.isPageOn()){
            sql.append(" limit ?,? ");
            vals.add(model.getRowStart());
            vals.add(model.getPageLimit());
        }
        return vals;
    }

    @Override
    public Integer selectCount(EmployeeModel model) {
        StringBuffer sql = new StringBuffer("select count(1) from ").append(table);
        List<Object> vals = appendWhere(model,sql);
        return JDBCUtil.queryInt(sql.toString(),vals);
    }

    @Override
    public String resetPass(EmployeeModel model) {
        String sql = String.format("update %s set pass = ? where code = ?", table);
        return JDBCUtil.update(sql,model.getPass(),model.getCode());
    }

}

